from common.sqlalchemy_package import *
from flask import g
from datetime import datetime
from datetime import timedelta


class Total(BaseModel):
    __tablename__ = 'total'
    total_id = mapped_column(Integer, primary_key=True)  # 总金额id
    car_number=mapped_column(String) #车牌号
    time = mapped_column(DateTime)  # 操作时间
    money = mapped_column(Integer)  # 金额
    remarks=mapped_column(Text) #备注

    # 添加金额流水
    @staticmethod
    def add_total(car_number, time, money, remarks):
        try:
            total = Total(car_number=car_number, time=time,money=money,remarks=remarks)
            g.db_session.add(total)
            g.db_session.commit()
            return 0
        except Exception as e:
            return -1

    # 获取总金额流水
    @staticmethod
    def query_total_all(page):
        per_page = 8  # 每页返回的数据量
        start_index = (int(page) - 1) * per_page  # 计算每页数据的起始索引
        query = g.db_session.query(Total)
        # 查询满足条件的记录总数
        total_count = query.count()
        # 计算总页数
        total_pages = (total_count + per_page - 1) // per_page
        # 分页查询并逆向排序
        results = query.order_by(Total.total_id.desc()).limit(per_page).offset(start_index).all()
        return results, total_pages

    #根据车牌号模糊搜索
    @staticmethod
    def total_search(car_number):
        return g.db_session.query(Total).filter(Total.car_number.like('%' + car_number + '%')).order_by(
            Total.total_id.desc()).all()



    # 根据日期模糊查询
    # def link_time(time):
    #     query = (
    #         g.db_session.query(Total.time, Total.money)
    #         .filter(Total.time.like('%' + time + '%'))
    #         .all()
    #     )
    #     result = {}
    #     for row in query:
    #         date_str = row[0].strftime('%Y-%m-%d')  # 将 datetime 对象转换为字符串
    #         if date_str in result:
    #             result[date_str] += row[1]
    #         else:
    #             result[date_str] = row[1]
    #     return result
    @staticmethod
    def count_money_time(end_date_str):
        end_date = datetime.strptime(end_date_str, '%Y-%m-%d').date()
        start_date = end_date - timedelta(days=6)
        result = {}

        for i in range(7):
            date = start_date + timedelta(days=i)
            date_str = date.strftime('%Y-%m-%d')

            query_result = (
                g.db_session.query(func.sum(Total.money))
                .filter(Total.time.like(f'%{date_str}%'))
                .scalar()
            )
            total_money = query_result if query_result else 0

            result[date_str] = total_money

        return result



